﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class GiangVien
    {
        //private const String SConn = "Data Source=DUCLINH-PC;Initial Catalog=HOUSMS;Persist Security Info=True;User ID=sa;Password=minhduc";
        private static string SConn = ConfigurationManager.ConnectionStrings["DucLM"].ConnectionString;
        #region "Lấy dữ liệu về giảng viên để đưa ra  datagridview"
        public DataSet Binding()
        {
            var ds = new DataSet();
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("GetAllGiangVien", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.Fill(ds, "LoadData");
            return ds;
        }
        #endregion


        #region "Thêm mới giảng viên"
        public int AddGiangVien(String tenGiangVien, String soDt, int maMonHoc)
        {
            var conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = new SqlCommand("AddGiangVien", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.Parameters.AddWithValue("@TenGiangVien", tenGiangVien);
                cmd.Parameters.AddWithValue("@SoDT", soDt);
                cmd.Parameters.AddWithValue("@MaMonHoc", maMonHoc);
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region "Sửa thông tin giảng viên"
        public int EditGiangVien(string tenGiangVien, string soDT, int maMonHoc)
        {
            var conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = new SqlCommand("EditGiangVien", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.Parameters.AddWithValue("@TenGiangVien", tenGiangVien);
                cmd.Parameters.AddWithValue("@SoDT", soDT);
                cmd.Parameters.AddWithValue("@MaMonHoc", maMonHoc);
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region "Xóa thông tin giảng viên"
        public int DeleteGiangVien(string tenGiangVien)
        {
            var conn = new SqlConnection(SConn);
            conn.Open();
            var cmd = new SqlCommand("DeleteGiangVien", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                cmd.Parameters.AddWithValue("@TenGiangVien", tenGiangVien);
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region "Tìm kiếm theo tên giảng viên"
        public DataSet SearchByTenGiangVien(string tenGiangVien)
        {
            var ds = new DataSet();
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("SearchByTenGiangVien", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("@TenGiangVien", tenGiangVien);
            da.Fill(ds, "SearchByTenGiangVien");
            return ds;
        }
        #endregion

        #region "Tìm kiếm theo tên môn học"
        public DataSet SearchByTenMonHoc(string tenMonHoc)
        {
            var ds = new DataSet();
            var conn = new SqlConnection(SConn);
            var da = new SqlDataAdapter("SearchByTenMonHoc", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("@TenMonHoc", tenMonHoc);
            da.Fill(ds, "SearchByTenMonHoc");
            return ds;
        }
        #endregion
    }
}
